# 1. 初始化django框架
import os
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "demo02.settings")
import django
django.setup()
from django.db.models import F, Q, Sum, Avg, Count, Max, Min
# 一，添加数据
# - 1.创建名为西游记的图书，添加到数据库中
import datetime

from ormapp.models import BookInfo, HeroInfo

# book = BookInfo(btitle= '西游记', bpub_date=datetime.date(2019, 1, 1))
# book.save()
# book = BookInfo.objects.create(btitle= '新书', bpub_date=datetime.date(2019, 1, 1))
# - 2.创建孙悟空的英雄，关联西游记，添加到书库中
# 取西游记这本书
# book = BookInfo.objects.get(id=5)  # 根据id取
# book = BookInfo.objects.get(btitle='西游记')  # 根据title取
# print(book.btitle)
# hero = HeroInfo(hname='孙悟空', hgender=0, hbook=book)
# hero.save()
#
# 二，查询数据
#
# - ===一、基本查询===
# get, all, count
# book = BookInfo.objects.get(id=1)
# print(book)
# books = BookInfo.objects.all()
# print(books)
# print(BookInfo.objects.count())
# - 1.查询编号为1的图书【两种方式】
# BookInfo.objects.filter(id__exact=1)

# - 2.查询所有图书的数量
# BookInfo.objects.count()
# - 3.查询书名包含雪山的图书【开头，结尾】
# print(BookInfo.objects.get(btitle__contains='雪山'))  # 书名任何位置 包含 '雪山' 都可以
# print(BookInfo.objects.get(btitle__startswith='雪山'))  # 书名必须是以 '雪山' 开头的
# print(BookInfo.objects.get(btitle__endswith='雪山'))  # 书名必须是以 '雪山' 结尾的
# print(BookInfo.objects.filter(btitle__contains='雪山'))  # 书名任何位置 包含 '雪山' 都可以
# print(BookInfo.objects.filter(btitle__startswith='雪山'))  # 书名必须是以 '雪山' 开头的
# print(BookInfo.objects.filter(btitle__endswith='雪山'))  # 书名必须是以 '雪山' 结尾的
# - 4.查询英雄描述为空的
# print(HeroInfo.objects.filter(hcomment__isnull=True))  # hcomment=''

# - 5.查询书籍编号为1，3，5的图书
# print(BookInfo.objects.filter(id__in=[1,3,5]))
# - 6.查询编号大于3的图书
# print(BookInfo.objects.filter(id__gt=3))

# - 7.查询编号大于等于3的图书
# print(BookInfo.objects.filter(id__gte=3))

# - 8.查询编号不等于3的图书
# print(BookInfo.objects.exclude(id=3))
# - 9.查询1980年发表的图书
# print(BookInfo.objects.filter(bpub_date__year=1980))
# print(BookInfo.objects.filter(bpub_date__month=1))
# - 10.查询1980年1月1号之后发表的图书
# print(BookInfo.objects.filter(bpub_date__gt=datetime.date(1990,1,1)))
#
# - ===二、FQ查询===
# - 11.查询阅读量大于等于评论量的图书
# print(BookInfo.objects.filter(bread__gte=F('bcomment')))
# - 12.查询阅读量大于等于2倍评论量的图书
# print(BookInfo.objects.filter(bread__gt=F('bcomment')*2))
# - 13.查询阅读量大于20，并且编号小于3的图书
# print(BookInfo.objects.filter(bread__gt=20, id__lt=3))
# - 14.查询阅读量大于20，或编号小于3的图书，只能使用Q对象实现
# print(BookInfo.objects.filter(Q(bread__gt=20) | Q(id__lt=3)))  # & 并且, | 或者, ~ 非
# - 15.查询编号不等于3的图书，使用Q对象实现
# print(BookInfo.objects.filter(~Q(id=3)))
# - 16.查询所有阅读量的总和
# print(BookInfo.objects.aggregate(Sum('bread'), Avg('bread'), Max('bread'), Min('bread')))
# - 17.查询最大的评论量
# print(BookInfo.objects.aggregate(Max('bcomment')))
# - 18.查询所有图书，按照阅读量排序，升序【降序】
# print(BookInfo.objects.all().order_by('bread'))  # 升序
# print(BookInfo.objects.all().order_by('-bread'))  # 降序

# 三，修改数据
# - 1.修改书籍名为雪山飞狐的书籍，改为雪山飞猫
# book = BookInfo.objects.get(btitle='雪山飞狐')
# book.btitle = '雪山飞猫'
# book.save()
# 1.1 修改阅读量为0 的数据, 改为阅读量 100
# BookInfo.objects.filter(bread=0).update(bread=100)
# - 2.修改英雄名为孙悟空的英雄，改名为孙大圣
# hero = HeroInfo.objects.get(hname='孙悟空')
# hero.hname = '孙大圣'
# hero.save()

# 四，删除数据
# - 1.删除编号为 10 的英雄
# hero = HeroInfo.objects.get(id=10)
# hero.delete()
# - 2.删除英雄名为 郭靖 的英雄
# HeroInfo.objects.filter(hname='郭靖').delete()
#
# books = BookInfo.objects.all()
"""
SELECT `tb_books`.`id`, `tb_books`.`btitle`, `tb_books`.`bpub_date`, `tb_books`.`bread`, `tb_books`.`bcomment`, `tb_books`.`is_delete` FROM `tb_books`
"""
# books = BookInfo.objects.all()[0:2]
"""
SELECT `tb_books`.`id`, `tb_books`.`btitle`, `tb_books`.`bpub_date`, `tb_books`.`bread`, `tb_books`.`bcomment`, `tb_books`.`is_delete` FROM `tb_books` LIMIT 2
"""

# books = BookInfo.objects.all().filter(btitle='abc')[0:2]
"""
SELECT `tb_books`.`id`, `tb_books`.`btitle`, `tb_books`.`bpub_date`, `tb_books`.`bread`, `tb_books`.`bcomment`, `tb_books`.`is_delete` FROM `tb_books` WHERE `tb_books`.`btitle` = abc LIMIT 2
"""
# - ===三、关联过滤查询===
# - 19.查询书籍编号为1，中的所有英雄
# 1,先取到一个确定的对象
# book = BookInfo.objects.get(id=1)
# 2,查询相关联的对象或对象集合
# heros = book.heroinfo_set.all()
# print(heros)
# - 20.查询书籍名称为雪山飞狐的，所有的英雄
# book = BookInfo.objects.get(btitle='雪山飞猫')
# heros = book.heroinfo_set.all()
# print(heros)
# - 21.查询英雄编号为1，所属的图书
# hero = HeroInfo.objects.get(id=2)
# print(hero.hbook)

# - 22.查询英雄中有乔峰的书籍
# book = BookInfo.objects.filter(heroinfo__hname='乔峰')
# print(book)
# - 23.查询英雄名字中包含‘郭’的书籍
# book = BookInfo.objects.filter(heroinfo__hname__contains='黄').distinct()
# print(book)
# - 24.查询书籍阅读量大于30的书籍中的所有英雄
# hero = HeroInfo.objects.filter(hbook__bread__gt=30)
# print(hero)


# 测试自定义管理类

# print(BookInfo.myobjects.all())
# print(BookInfo.myobjects.filter_hot())
# print(BookInfo.myobjects.all().filter_hot())

# book = BookInfo.myobjects.create_hot(btitle='热门新书', bpub_date=datetime.date(2019, 5, 5))


from ormapp.models import Teacher, Student

# Teacher.myobjects.create_zhang('三')
#
Student.myobjects.create_zhang('五')






